//
//  DBFrameService.m
//  iphone.mobileoa
//
//  Created by 李亚春 on 13-5-23.
//  Copyright (c) 2013年 Epoint. All rights reserved.
//

#import "DBFrameService.h"
#import "OuInfoModel.h"
#import "CategoryInfoModel.h"

@implementation DBFrameService


+(void)setConfigValue:(NSString *)key value:(NSString *)value
{
    sqlite3 *framedb = [DbUtil getFrameDB];
    char *errorMsg;
    NSString *delete = [NSString stringWithFormat:@"delete from config where key = '%@' ",key];
    NSString *msInsertSql = [NSString stringWithFormat:@"insert into config values('%@','%@')",key,value];
    sqlite3_exec(framedb,[delete UTF8String],NULL,NULL,&errorMsg);
    sqlite3_exec(framedb, [msInsertSql UTF8String], NULL, NULL, &errorMsg);
    sqlite3_close(framedb);
    if (errorMsg!=NULL) {
        NSLog(@"ERR:%s",errorMsg);
    }
}

+(NSString *)getConfigValue:(NSString *)key
{
    sqlite3 *framedb = [DbUtil getFrameDB];
    NSString *selectSql=[NSString stringWithFormat:@"select value from config where key = '%@'",key];
    sqlite3_stmt *statement;
    NSString *nsvalue = @"";
    sqlite3_prepare_v2(framedb, [selectSql UTF8String], -1, &statement, nil);
    while (sqlite3_step(statement)==SQLITE_ROW) {
        char *value=(char *)sqlite3_column_text(statement, 0);
        nsvalue = [NSString stringWithUTF8String:value];
    }
    sqlite3_finalize(statement);
    sqlite3_close(framedb);
    return nsvalue;
}

+(NSString *)getWebServiceUrl
{
    return [self getConfigValue:EP_ConfigKey_wsurl];
}

+(int)getUserCount
{
    sqlite3 *framedb = [DbUtil getFrameDB];
    NSString *selectSql=[NSString stringWithFormat:@"select count(*) from user"];
    sqlite3_stmt *statement;
    sqlite3_prepare_v2(framedb, [selectSql UTF8String], -1, &statement, nil);
    int count;
    while (sqlite3_step(statement)==SQLITE_ROW) {
        count = sqlite3_column_int(statement, 0);
    }
    sqlite3_finalize(statement);
    sqlite3_close(framedb);
    return count;
}

+(int)getOuCount
{
    sqlite3 *framedb = [DbUtil getFrameDB];
    NSString *selectSql=[NSString stringWithFormat:@"select count(*) from ou"];
    sqlite3_stmt *statement;
    sqlite3_prepare_v2(framedb, [selectSql UTF8String], -1, &statement, nil);
    int count;
    while (sqlite3_step(statement)==SQLITE_ROW) {
        count = sqlite3_column_int(statement, 0);
    }
    sqlite3_finalize(statement);
    sqlite3_close(framedb);
    return count;
}

+(NSMutableArray *)getWebInfoCategoryByParent:(NSString *)parentOuGuid
{
    sqlite3 *framedb = [DbUtil getFrameDB];
    
    NSString *selectSql=[NSString stringWithFormat:@"select * from webinfo_category where ParentCategoryGuid = '%@'",parentOuGuid];
    
    sqlite3_stmt *statement;
    sqlite3_prepare_v2(framedb, [selectSql UTF8String], -1, &statement, nil);
    
    NSMutableArray *dataArray = [[NSMutableArray alloc]init];
    while (sqlite3_step(statement)==SQLITE_ROW) {
        CategoryInfoModel *m = [[CategoryInfoModel alloc]init];
        m.CategoryName = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 0)];
        m.CategoryGuid = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 1)];
        m.ParentCategoryGuid = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 2)];
        m.IsTongzhiGongGao = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 3)];
        [dataArray addObject:m];
        [m release];
        
    }
    sqlite3_finalize(statement);
    sqlite3_close(framedb);
    return [dataArray autorelease];
}

+(NSString *)getTzggCategoryguid
{
    sqlite3 *framedb = [DbUtil getFrameDB];
    NSString *selectSql=[NSString stringWithFormat:@"select categoryguid from webinfo_category where IsTongzhiGongGao = '1'"];
    sqlite3_stmt *statement;
    sqlite3_prepare_v2(framedb, [selectSql UTF8String], -1, &statement, nil);
    NSString *guid;
    while (sqlite3_step(statement)==SQLITE_ROW) {
        guid = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 0)];
    }
    sqlite3_finalize(statement);
    sqlite3_close(framedb);
    return guid;
}

+(NSMutableArray *)getContactsListByParentGuid:(NSString *)_parentguid
{
    sqlite3 *framedb = [DbUtil getFrameDB];
    //获取部门
    NSString *ousql = [NSString stringWithFormat:@"select ouname,ouguid from ou where parentguid = '%@'",_parentguid];
    sqlite3_stmt *statement;
    sqlite3_prepare_v2(framedb, [ousql UTF8String], -1, &statement, nil);
    NSMutableArray *dataArray = [[NSMutableArray alloc]init];
    
    while (sqlite3_step(statement)==SQLITE_ROW)
    {
        NSMutableDictionary *dic = [[NSMutableDictionary alloc]init];
        NSString *ouname = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 0)];
        NSString *ouguid = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 1)];
        [dic setObject:ouname forKey:@"name"];
        [dic setObject:ouguid forKey:@"guid"];
        [dic setObject:@"ou" forKey:@"type"];
        [dataArray addObject:dic];
        [dic release];
    }
    
    //人员
    NSString *usersql = [NSString stringWithFormat:@"select displayname,userguid,title,ouname from user,ou where user.ouguid = ou.ouguid and user.ouguid = '%@'",_parentguid];
    sqlite3_prepare_v2(framedb, [usersql UTF8String], -1, &statement, nil);
    while (sqlite3_step(statement)==SQLITE_ROW) {
        NSMutableDictionary *dic = [[NSMutableDictionary alloc]init];
        NSString *displayname = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 0)];
        NSString *userguid = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 1)];
        NSString *ouname = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 3)];
        NSString *title = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 2)];
        [dic setObject:displayname forKey:@"name"];
        [dic setObject:userguid forKey:@"guid"];
        [dic setObject:@"user" forKey:@"type"];
        [dic setObject:ouname forKey:@"ouname"];
        [dic setObject:title forKey:@"title"];
        [dataArray addObject:dic];
        [dic release];
    }
    sqlite3_finalize(statement);
    sqlite3_close(framedb);
    return [dataArray autorelease];
}

+(NSMutableArray *)getContactsListByParentGuid_PersonChoose:(NSString *)_parentguid
{
    sqlite3 *framedb = [DbUtil getFrameDB];
    //获取部门
    NSString *ousql = [NSString stringWithFormat:@"select ouname,ouguid from ou where parentguid = '%@'",_parentguid];
    sqlite3_stmt *statement;
    sqlite3_prepare_v2(framedb, [ousql UTF8String], -1, &statement, nil);
    NSMutableArray *dataArray = [[NSMutableArray alloc]init];
    
    while (sqlite3_step(statement)==SQLITE_ROW)
    {
        NSMutableDictionary *dic = [[NSMutableDictionary alloc]init];
        NSString *ouname = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 0)];
        NSString *ouguid = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 1)];
        [dic setObject:ouname forKey:@"name"];
        [dic setObject:ouguid forKey:@"guid"];
        [dic setObject:@"ou" forKey:@"type"];
        [dataArray addObject:dic];
        [dic release];
    }
    
    //人员
    NSString *usersql = [NSString stringWithFormat:@"select displayname,userguid from user,ou where user.ouguid = ou.ouguid and user.ouguid = '%@'",_parentguid];
    sqlite3_prepare_v2(framedb, [usersql UTF8String], -1, &statement, nil);
    while (sqlite3_step(statement)==SQLITE_ROW) {
        NSMutableDictionary *dic = [[NSMutableDictionary alloc]init];
        NSString *displayname = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 0)];
        NSString *userguid = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 1)];
        [dic setObject:displayname forKey:@"name"];
        [dic setObject:userguid forKey:@"guid"];
        [dic setObject:@"user" forKey:@"type"];
        [dataArray addObject:dic];
        [dic release];
    }
    sqlite3_finalize(statement);
    sqlite3_close(framedb);
    return [dataArray autorelease];
}


+(NSMutableArray *)getContactsListByKeyWord:(NSString *)_keyword
{
    sqlite3 *framedb = [DbUtil getFrameDB];
    //获取部门
    NSString *ousql = [NSString stringWithFormat:@"select ouname,ouguid from ou where ouname like '%%%@%%' or namepyshou like '%%%@%%'",_keyword,_keyword];
    sqlite3_stmt *statement;
    sqlite3_prepare_v2(framedb, [ousql UTF8String], -1, &statement, nil);
    NSMutableArray *dataArray = [[NSMutableArray alloc]init];
    
    while (sqlite3_step(statement)==SQLITE_ROW)
    {
        NSMutableDictionary *dic = [[NSMutableDictionary alloc]init];
        NSString *ouname = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 0)];
        NSString *ouguid = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 1)];
        [dic setObject:ouname forKey:@"name"];
        [dic setObject:ouguid forKey:@"guid"];
        [dic setObject:@"ou" forKey:@"type"];
        [dataArray addObject:dic];
        [dic release];
    }
    
    //人员
    NSString *usersql = [NSString stringWithFormat:@"select displayname,userguid,title,ouname from user,ou where LOWER(user.ouguid) = LOWER(ou.ouguid) and (user.displayname like '%%%@%%' or user.namepyshou like '%%%@%%')",_keyword,_keyword];
    sqlite3_prepare_v2(framedb, [usersql UTF8String], -1, &statement, nil);
    while (sqlite3_step(statement)==SQLITE_ROW) {
        NSMutableDictionary *dic = [[NSMutableDictionary alloc]init];
        NSString *displayname = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 0)];
        NSString *userguid = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 1)];
        NSString *ouname = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 3)];
        NSString *title = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 2)];
        [dic setObject:displayname forKey:@"name"];
        [dic setObject:userguid forKey:@"guid"];
        [dic setObject:@"user" forKey:@"type"];
        [dic setObject:ouname forKey:@"ouname"];
        [dic setObject:title forKey:@"title"];
        [dataArray addObject:dic];
        [dic release];
    }
    sqlite3_finalize(statement);
    sqlite3_close(framedb);
    return [dataArray autorelease];
}

+(NSString *)getOuNameByOuGuid:(NSString *)_ouguid
{
    sqlite3 *framedb = [DbUtil getFrameDB];
    //获取部门
    NSString *ousql = [NSString stringWithFormat:@"select ouname from ou where ouguid = '%@'",_ouguid];
    sqlite3_stmt *statement;
    sqlite3_prepare_v2(framedb, [ousql UTF8String], -1, &statement, nil);
    NSString *ouname;
    
    while (sqlite3_step(statement)==SQLITE_ROW)
    {
        ouname = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 0)];
    }
    sqlite3_finalize(statement);
    sqlite3_close(framedb);
    return ouname;
}

+(NSMutableDictionary *)getUserDetailByUserguid:(NSString *)_userguid
{
    sqlite3 *framedb = [DbUtil getFrameDB];
    
    NSString *sql = [NSString stringWithFormat:@"select displayname ,sex,ou.ouname,title,mobile,telephoneoffice,telephonehome,email from user ,ou where  LOWER(user.ouguid) = LOWER(ou.ouguid) and userguid = '%@'",_userguid];
    sqlite3_stmt *statement;
    sqlite3_prepare_v2(framedb, [sql UTF8String], -1, &statement, nil);
    NSMutableDictionary *user = [[NSMutableDictionary alloc]init];
    
    while (sqlite3_step(statement)==SQLITE_ROW)
    {
        [user setObject:[NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 0)] forKey:@"displayname"];
        [user setObject:[NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 1)] forKey:@"sex"];
        
        [user setObject:[NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 2)] forKey:@"ouname"];
        [user setObject:[NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 3)] forKey:@"title"];
        [user setObject:[NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 4)] forKey:@"mobile"];
        [user setObject:[NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 5)] forKey:@"teleoffice"];
        
        [user setObject:[NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 6)] forKey:@"telehome"];
        [user setObject:[NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 7)] forKey:@"mail"];
    }
    sqlite3_finalize(statement);
    sqlite3_close(framedb);
    return [user autorelease];
}

+(NSMutableArray *)getOunameArrayByGuids:(NSMutableArray *)guids
{

    NSMutableArray *ounames = [[NSMutableArray alloc]init];
    for (int i=0; i<guids.count; i++) {
        NSMutableDictionary *params = [[NSMutableDictionary alloc]init];
        [params setObject:[guids objectAtIndex:i] forKey:@"guid"];
        [params setObject:[self getOuNameByOuGuid:[guids objectAtIndex:i]] forKey:@"name"];
        [ounames addObject:params];
        [params release];
    }

    return [ounames autorelease];
}

+(NSMutableDictionary *)getParentOuByOuGuid:(NSString *)_ouguid
{
    sqlite3 *framedb = [DbUtil getFrameDB];
    //获取部门
    NSString *ousql = [NSString stringWithFormat:@"select ouname,ouguid from ou where ouguid = (select parentguid from ou where ouguid = '%@')",_ouguid];
    sqlite3_stmt *statement;
    sqlite3_prepare_v2(framedb, [ousql UTF8String], -1, &statement, nil);
    NSMutableDictionary *params = [[NSMutableDictionary alloc]init];
    while (sqlite3_step(statement)==SQLITE_ROW)
    {
        [params setObject:[NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 0)] forKey:@"ouname"];
        [params setObject:[NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 1)] forKey:@"ouguid"];
    }
    sqlite3_finalize(statement);
    sqlite3_close(framedb);
    return [params autorelease];
}

+(NSMutableArray *)getParentOusByOuguid:(NSString *)_ouguid
{
    NSMutableArray *ous = [[NSMutableArray alloc]init];
    NSString *zouguid = _ouguid;
    for (int i=0; i<10; i++) {
        NSMutableDictionary *params = [[self getParentOuByOuGuid:zouguid]retain];
        NSString *ouguid = [params objectForKey:@"ouguid"];
        NSString *ouname = [params objectForKey:@"ouname"];
        if (ouname.length>0||ouguid.length>0) {
            [ous addObject:params];
            zouguid = ouguid;
        }
        else
        {
            break;
        }
    }
    
    return ous;
}

@end
